/**
 * Copyright (C), 2020, JieYi Software System Co., Ltd.
 * All rights reserved.
 * FileName:       ExcuteAndShowService.java
 *
 * @Description:
 * @author: feiwe
 * Modification History:
 * Date         Author        Version        Discription
 * -------------------------------------------------------
 * 2020/11/17      feiwe                         add
 */


package com.fengwei.service;

import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.system.SystemUtil;
import com.fengwei.bean.Page;
import com.fengwei.config.CommonProperty;
import com.fengwei.dao.MdmsUserCommonsqlMapper;
import com.fengwei.dao.MdmsUserLogMapper;
import com.fengwei.executor.AbstractDbExecutor;
import com.fengwei.form.MdmsDatabase;
import com.fengwei.form.MdmsUser;
import com.fengwei.form.MdmsUserCommonsql;
import com.fengwei.form.MdmsUserLog;
import com.fengwei.requestdata.excuteAndShow.ExcuteAndShowAddCommonSqlData;
import com.fengwei.requestdata.excuteAndShow.ExcuteAndShowDelCommonSqlData;
import com.fengwei.requestdata.excuteAndShow.ExcuteAndShowEditCommonSqlData;
import com.fengwei.requestdata.excuteAndShow.ExcuteAndShowExecSqlData;
import com.fengwei.requestdata.excuteAndShow.ExcuteAndShowExportDataData;
import com.fengwei.requestdata.excuteAndShow.ExcuteAndShowGetCommonSqlData;
import com.fengwei.requestdata.excuteAndShow.ExcuteAndShowPagingListData;
import com.fengwei.util.GsonUtil;
import com.fengwei.util.MdmsConsts;
import com.fengwei.util.PltResult;
import com.fengwei.util.SnowflakeIdWorker;
import com.fengwei.util.SpringContextUtil;
import com.fengwei.util.SqlUtil;
import com.fengwei.util.VerifyException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.io.File;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ExcuteAndShowService {
    private final static Logger logger = LoggerFactory.getLogger(ExcuteAndShowService.class);

    @Autowired
    private MdmsUserLogMapper mdmsUserLogMapper;

    @Autowired
    private SnowflakeIdWorker snowflakeIdWorker;

    @Autowired
    private CommonProperty commonProperty;

    @Autowired
    private MdmsUserCommonsqlMapper mdmsUserCommonsqlMapper;

    public Map<String, Object> execSql(ExcuteAndShowExecSqlData excuteAndShowExecSqlData, HttpServletRequest request, MdmsUser mdmsUser) throws Exception {
        Map<String, Object> returnData = new HashMap<>();
        HttpSession session = request.getSession();
        MdmsDatabase mdmsDatabase = (MdmsDatabase) session.getAttribute("currentDatabaseId");
        if (null == mdmsDatabase) {
            throw new VerifyException(PltResult.RESULT_CF303);
        }

        List<String> sqlList = excuteAndShowExecSqlData.getSqlList();

        //不同的角色支持的SQL不一样
        for (String sql : sqlList) {
            sql = SqlUtil.trimLeft(sql);
            if (MdmsConsts.USER_TYPE_99.equals(mdmsUser.getUserType())) {
                throw new VerifyException(PltResult.RESULT_CF305);
            }

            if (MdmsConsts.USER_TYPE_0.equals(mdmsUser.getUserType())) {
                //普通用户
                if (!sql.toLowerCase().startsWith("select")) {
                    throw new VerifyException(PltResult.RESULT_CF306);
                }
            }

            if (MdmsConsts.USER_TYPE_1.equals(mdmsUser.getUserType())) {
                //普通用户
                if (!sql.toLowerCase().startsWith("select") && !sql.toLowerCase().startsWith("insert") && !sql.toLowerCase().startsWith("update") && !sql.toLowerCase().startsWith("delete")) {
                    throw new VerifyException(PltResult.RESULT_CF307);
                }
            }
        }

        logger.debug("GsonUtil.toJson(sqlList):" + GsonUtil.toJson(sqlList, false));
        AbstractDbExecutor abstractDbExecutor = (AbstractDbExecutor) SpringContextUtil.getBean(mdmsDatabase.getDatabaseType() + "Executor");
        String url = abstractDbExecutor.genUrl(mdmsDatabase.getDatabaseType(), mdmsDatabase.getDatabaseHost(), mdmsDatabase.getDatabasePort(),
                mdmsDatabase.getDatabaseDbname());
        Connection connection = null;
        try {
            connection = abstractDbExecutor.getConnection(mdmsDatabase.getId() + "", url, mdmsDatabase.getDatabaseUser(), mdmsDatabase.getDatabasePassword());
            List resultList = new ArrayList();
            StringBuffer sbRunLog = new StringBuffer();
            for (String sql : sqlList) {
                sql = SqlUtil.trimLeft(sql);
                long start = System.currentTimeMillis();
                long count = 0;
                if (sql.toLowerCase().startsWith("select")) {
                    Page page = new Page();
                    page.setPageNo(1);
                    page.setPageSize(10);
                    Page<Map<String, Object>> pageResult = abstractDbExecutor.executeSelectSql(page, connection, sql);
                    logger.debug("pageResult:" + GsonUtil.toJson(pageResult, false));
                    pageResult.setSql(sql);
                    resultList.add(pageResult);

                    count = pageResult.getTotalCount();
                } else {
                    count = abstractDbExecutor.executeInsertUpdateDeleteSql(connection, sql, commonProperty.getDatachangeThreshold());
                }

                sbRunLog.append("【命令】 " + sql + "\r\n");
                sbRunLog.append("影响 " + count + " 行\r\n");
                sbRunLog.append(" 运行时间： " + (System.currentTimeMillis() - start) + "毫秒\r\n");
                sbRunLog.append("\r\n");

                //非select要记录执行日志
                if (!sql.toLowerCase().startsWith("select")) {
                    MdmsUserLog mdmsUserLog = new MdmsUserLog();
                    mdmsUserLog.setId(snowflakeIdWorker.nextId());
                    mdmsUserLog.setApplyUserId(mdmsUser.getId());
                    mdmsUserLog.setApplyUserName(mdmsUser.getUserName());
                    mdmsUserLog.setDatabaseId(mdmsDatabase.getId());
                    mdmsUserLog.setDatabaseName(mdmsDatabase.getDatabaseName());
                    mdmsUserLog.setHostIpport(getIpAddr(request));
                    mdmsUserLog.setRunSql(sql);
                    mdmsUserLog.setAffectLineNum((int) count);
                    mdmsUserLogMapper.insert(mdmsUserLog);
                }
            }

            returnData.put("resultList", resultList);
            returnData.put("runLog", sbRunLog.toString());
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            if (e instanceof VerifyException) {
                throw e;
            } else {
                VerifyException verifyException = new VerifyException(PltResult.RESULT_CF304);
                verifyException.setErrorDesc(PltResult.RESULT_CF304.getpName() + ":" + e.getMessage());
                // 数据库不存在
                throw verifyException;
            }
        } finally {
            abstractDbExecutor.closeConnection(connection);
        }

        return returnData;
    }

    public Map<String, Object> pagingList(ExcuteAndShowPagingListData excuteAndShowPagingListData, HttpServletRequest request, MdmsUser mdmsUser) throws SQLException {
        Map<String, Object> returnData = new HashMap<>();
        HttpSession session = request.getSession();
        MdmsDatabase mdmsDatabase = (MdmsDatabase) session.getAttribute("currentDatabaseId");
        if (null == mdmsDatabase) {
            throw new VerifyException(PltResult.RESULT_CF303);
        }

        //不同的角色支持的SQL不一样
        String sql = SqlUtil.trimLeft(excuteAndShowPagingListData.getSql());
        if (MdmsConsts.USER_TYPE_99.equals(mdmsUser.getUserType())) {
            throw new VerifyException(PltResult.RESULT_CF305);
        }

        logger.debug("sql:" + sql);
        AbstractDbExecutor abstractDbExecutor = (AbstractDbExecutor) SpringContextUtil.getBean(mdmsDatabase.getDatabaseType() + "Executor");
        String url = abstractDbExecutor.genUrl(mdmsDatabase.getDatabaseType(), mdmsDatabase.getDatabaseHost(), mdmsDatabase.getDatabasePort(),
                mdmsDatabase.getDatabaseDbname());
        Connection connection = null;
        try {
            connection = abstractDbExecutor.getConnection(mdmsDatabase.getId() + "", url, mdmsDatabase.getDatabaseUser(), mdmsDatabase.getDatabasePassword());
            Page page = new Page();
            page.setPageNo(excuteAndShowPagingListData.getPage());
            page.setPageSize(excuteAndShowPagingListData.getLimit());
            Page<Map<String, Object>> pageResult = abstractDbExecutor.executeSelectSql(page, connection, sql);
            logger.debug("pageResult:" + GsonUtil.toJson(pageResult, false));
            pageResult.setSql(sql);

            returnData.put("pageResult", pageResult);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            // 数据库不存在
            throw new VerifyException(PltResult.RESULT_CF304);
        } finally {
            abstractDbExecutor.closeConnection(connection);
        }

        return returnData;
    }

    public Map<String, Object> exportData(ExcuteAndShowExportDataData excuteAndShowExportDataData, HttpServletRequest request, MdmsUser mdmsUser) throws Exception {
        Map<String, Object> returnData = new HashMap<>();
        HttpSession session = request.getSession();
        MdmsDatabase mdmsDatabase = (MdmsDatabase) session.getAttribute("currentDatabaseId");
        if (null == mdmsDatabase) {
            throw new VerifyException(PltResult.RESULT_CF303);
        }

        //不同的角色支持的SQL不一样
        String sql = SqlUtil.trimLeft(excuteAndShowExportDataData.getSql());
        if (MdmsConsts.USER_TYPE_99.equals(mdmsUser.getUserType())) {
            throw new VerifyException(PltResult.RESULT_CF305);
        }

        logger.debug("sql:" + sql);
        AbstractDbExecutor abstractDbExecutor = (AbstractDbExecutor) SpringContextUtil.getBean(mdmsDatabase.getDatabaseType() + "Executor");
        String url = abstractDbExecutor.genUrl(mdmsDatabase.getDatabaseType(), mdmsDatabase.getDatabaseHost(), mdmsDatabase.getDatabasePort(),
                mdmsDatabase.getDatabaseDbname());
        Connection connection = null;

        String excelName = "sql" + snowflakeIdWorker.nextId() + ".xlsx";
        cn.hutool.system.UserInfo userInfo = SystemUtil.getUserInfo();
        FileUtil.mkdir(userInfo.getHomeDir() + File.separator + "exportData");
        ExcelWriter writer = ExcelUtil.getWriter(userInfo.getHomeDir() + File.separator + "exportData" + File.separator + excelName);
        try {
            connection = abstractDbExecutor.getConnection(mdmsDatabase.getId() + "", url, mdmsDatabase.getDatabaseUser(), mdmsDatabase.getDatabasePassword());
            Page page = new Page();
            page.setPageNo(1);
            page.setPageSize(1000);
            Page<Map<String, Object>> pageResult = abstractDbExecutor.executeSelectSql(page, connection, sql);


            /*将查询到的记录写到excel当中*/
            List<Map<String, Object>> result = pageResult.getResult();
            // 一次性写出内容，使用默认样式，强制输出标题
            writer.write(result, true);

            returnData.put("excelName", excelName);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            // 数据库不存在
            throw new VerifyException(PltResult.RESULT_CF304);
        } finally {
            abstractDbExecutor.closeConnection(connection);
        }

        return returnData;
    }

    public Map<String, Object> getCommonSql(ExcuteAndShowGetCommonSqlData excuteAndShowGetCommonSqlData, HttpServletRequest request, MdmsUser mdmsUser) {
        Map<String, Object> returnData = new HashMap<>();
        Map mapMdmsUserCommonsqlSelectAll = new HashMap();
        mapMdmsUserCommonsqlSelectAll.put("userId", mdmsUser.getId());
        List<MdmsUserCommonsql> list = mdmsUserCommonsqlMapper.selectAll(mapMdmsUserCommonsqlSelectAll);
        returnData.put("list", list);
        return returnData;
    }

    public Map<String, Object> addCommonSql(ExcuteAndShowAddCommonSqlData excuteAndShowAddCommonSqlData, HttpServletRequest request, MdmsUser mdmsUser) {
        Map<String, Object> returnData = new HashMap<>();
        MdmsUserCommonsql mdmsUserCommonsql = new MdmsUserCommonsql();
        mdmsUserCommonsql.setId(snowflakeIdWorker.nextId());
        mdmsUserCommonsql.setName(excuteAndShowAddCommonSqlData.getName());
        mdmsUserCommonsql.setUserId(mdmsUser.getId());
        mdmsUserCommonsql.setCommonSql(excuteAndShowAddCommonSqlData.getCommonSql());
        mdmsUserCommonsqlMapper.insert(mdmsUserCommonsql);
        return returnData;
    }

    public Map<String, Object> editCommonSql(ExcuteAndShowEditCommonSqlData excuteAndShowEditCommonSqlData, HttpServletRequest request, MdmsUser mdmsUser) {
        Map<String, Object> returnData = new HashMap<>();
        Map mapSelectByPrimaryKey = new HashMap();
        mapSelectByPrimaryKey.put("id", excuteAndShowEditCommonSqlData.getId());
        MdmsUserCommonsql mdmsUserCommonsql = mdmsUserCommonsqlMapper.selectByPrimaryKey(mapSelectByPrimaryKey);
        if (null == mdmsUserCommonsql) {
            throw new VerifyException(PltResult.RESULT_CF310);
        } else {
            MdmsUserCommonsql mdmsUserCommonsqlUpdate = new MdmsUserCommonsql();
            mdmsUserCommonsqlUpdate.setId(mdmsUserCommonsql.getId());
            mdmsUserCommonsqlUpdate.setName(excuteAndShowEditCommonSqlData.getName());
            mdmsUserCommonsqlUpdate.setCommonSql(excuteAndShowEditCommonSqlData.getCommonSql());
            mdmsUserCommonsqlMapper.update(mdmsUserCommonsqlUpdate);
        }

        return returnData;
    }

    public Map<String, Object> delCommonSql(ExcuteAndShowDelCommonSqlData excuteAndShowDelCommonSqlData, HttpServletRequest request, MdmsUser mdmsUser) {
        Map<String, Object> returnData = new HashMap<>();
        Map mapSelectByPrimaryKey = new HashMap();
        mapSelectByPrimaryKey.put("id", excuteAndShowDelCommonSqlData.getId());
        MdmsUserCommonsql mdmsUserCommonsql = mdmsUserCommonsqlMapper.selectByPrimaryKey(mapSelectByPrimaryKey);
        if (null == mdmsUserCommonsql) {
            throw new VerifyException(PltResult.RESULT_CF310);
        } else {
            MdmsUserCommonsql mdmsUserCommonsqlDel = new MdmsUserCommonsql();
            mdmsUserCommonsqlDel.setId(excuteAndShowDelCommonSqlData.getId());
            mdmsUserCommonsqlMapper.delete(mdmsUserCommonsqlDel);
        }

        return returnData;
    }

    private String getIpAddr(HttpServletRequest request) {
        String ip = request.getHeader("X-Real-IP");//先从nginx自定义配置获取
        logger.debug("ip:" + ip + " after get X-Real-IP");
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeader("X-Forwarded-For");
        }
        logger.debug("ip:" + ip + " after get X-Forwarded-For");
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeader("Proxy-Client-IP");
        }
        logger.debug("ip:" + ip + " after get Proxy-Client-IP");
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeader("WL-Proxy-Client-IP");
        }
        logger.debug("ip:" + ip + " after get WL-Proxy-Client-IP");
        if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getRemoteAddr();
        }
        logger.debug("ip:" + ip + " after get RemoteAddr");
        return ip;
    }


}
